import datetime
import math
import pymysql
from sqlalchemy import create_engine
from AppStoreConnect import Product, subscription_events



_sales_types = {
    'New' : 1,
    'Renewal' : 2
}

_sales_category_types = {
    'Health & Fitness' : 8,
}

_sales_device_types = {
    'iPhone' : 1,
    'iPad' : 2,
    'Apple Watch' : 3,
    'Desktop' : 4,
    'Apple Vision' : 5,
    'iPod touch' : 6,
    'Apple TV' : 7,
}

_sales_period_types = {
    '7 Days' : 7,
    '1 Month' : 30,
    '3 Months' : 90,
    '1 Year' : 365
}

_standard_subscription_duration = {
    '7 Days' : 7,
    '1 Month' : 30,
    '3 Months' : 90,
    '6 Months' : 180,
    '1 Year' : 365
}

_subscription_offer_type = {
    'Free Trial' : 1,
    'Pay as You Go' : 2,
    'Pay Up Front' : 3
}

_subscription_offer_duration = {
    '3 Days' : 3,
    '7 Days' : 7
}

_cancellation_reason = {
    'Canceled' : 1,
    'Billing Issue' : 2
}


class AppDB:

    engine = create_engine('mysql+pymysql://appconnect:pobxoj-Mopsy5-bycqac@47.116.122.58:3306/app')

    def __init__(self):
        self.db = pymysql.connect(host="47.116.122.58", user="appconnect", passwd="pobxoj-Mopsy5-bycqac", db="app")
        
    def add_summary_daily_report(self, date:datetime.datetime, report:list):
        print('Adding summary daily report to the database. Date:', date, 'Report count:', len(report))

        exchange_rates = self.get_exhcange_rates(date=date)
        
        cursor = self.db.cursor()
    
        # 先删除表中begin_date = date 的数据
        if len(report) > 0:
            sql = f"DELETE FROM summary_daily WHERE begin_date = '{date}'"
            affect_count = cursor.execute(sql)
            self.db.commit()   
            print('Deleted', affect_count, 'records from summary_daily. Date:', date)
        
        for record in report:
            currency_code = record['Currency of Proceeds'] if str.strip( record['Currency of Proceeds']) != '' else record['Customer Currency']
            assert(currency_code in exchange_rates)
            record['exchange_rate'] = exchange_rates[currency_code]
            self._add_summary_daily_record(cursor=cursor, record=record) 
            
        cursor.close()
    
    def _add_summary_daily_record(self, cursor, record:dict):
        column_values = dict()

        _report_to_db_sales_summary_column_names = {
            'SKU' : 'sku',
            'Title' : 'title',
            'Version' : 'version',
            'Product Type Identifier' : 'product_type_identifier',
            'Units' : 'units',
            'Developer Proceeds' : 'developer_proceeds',
            'Begin Date' : 'begin_date',
            'End Date' : 'end_date',
            'Customer Currency' : 'customer_currency',
            'Country Code' : 'country_code',
            'Currency of Proceeds' : 'currency_of_proceeds',
            'Apple Identifier' : 'apple_identifier',
            'Customer Price' : 'custom_price',
            'Promo Code' : 'promo_code',
            'Parent Identifier' : 'parent_identifier',
            'Subscription' : 'subscription',
            'Period' : 'period',
            'Category' : 'category',
            'CMB' : 'cmb',
            'Device' : 'device',
            'Proceeds Reason' : 'proceeds_reason',
            # 'Preserved Pricing' : 'preserved_pricing',
            'Client' : 'client',
            'Order Type' : 'order_type',
            'exchange_rate' : 'exchange_rate',
        }
        
        for key in record.keys():
            if key not in _report_to_db_sales_summary_column_names:
                # print(f"Unknown column name: {key}")
                continue
            
            column_values[_report_to_db_sales_summary_column_names[key]] = record[key]
            
        columns = ', '.join(column_values.keys())
        
        placeholders = ', '.join(['%s'] * len(column_values))
        
        # Get the column values
        sql = f"INSERT INTO summary_daily ({columns}) VALUES ({placeholders})"
        
        # 如果为' '，则转换为mysql 的空值
        for key, value in column_values.items():
            if value == ' ':
                column_values[key] = None
            elif type(value) == float and math.isnan(value):
                column_values[key] = None
                
        column_values = self._convert_sales_summary_value_types(column_values)

        result = cursor.execute(sql, list(column_values.values()))
        if result:
            print('Record inserted successfully. Date:', column_values['begin_date'], '\tTitle:', column_values['title'], '\tPurchase Type:', column_values['product_type_identifier'], '\tUnits:', column_values['units'])
        self.db.commit()

        
    def _convert_sales_summary_value_types(self, column_values):

        if 'subscription' in column_values and column_values['subscription'] in _sales_types:
            column_values['subscription'] = _sales_types[column_values['subscription']]
        
        if 'category' in column_values and column_values['category'] in _sales_category_types:
            column_values['category'] = _sales_category_types[column_values['category']]
        
        if 'device' in column_values and column_values['device'] in _sales_device_types:
            column_values['device'] = _sales_device_types[column_values['device']]
            
        if 'period' in column_values and column_values['period'] in _sales_period_types:
            column_values['period'] = _sales_period_types[column_values['period']]
            
        if 'begin_date' in column_values:
            column_values['begin_date'] = datetime.datetime.strptime(column_values['begin_date'], '%m/%d/%Y').strftime('%Y-%m-%d')

        if 'end_date' in column_values:
            column_values['end_date'] = datetime.datetime.strptime(column_values['end_date'], '%m/%d/%Y').strftime('%Y-%m-%d')
        
        return column_values
            

    def add_subscription_event_report(self, report_date:datetime.datetime, report:list):
        print('Adding subscription event report to the database. Date:', report_date.strftime('%Y-%m-%d'), '\t Report count:', len(report))
        
        cursor = self.db.cursor()
        
        # 先删除表中report_date = report_date 的数据
        sql = f"DELETE FROM subscription_event WHERE report_date = '{report_date.strftime('%Y-%m-%d')}'"
        affect_count = cursor.execute(sql)
        self.db.commit()
        print('Deleted', affect_count, 'records from subscription_event. Date:', report_date.strftime('%Y-%m-%d'))
        
        for record in report:
            self._add_subscription_record(cursor=cursor, report_date=report_date, record=record) 
            
        cursor.close()
        
    def _add_subscription_record(self, cursor, report_date:datetime.datetime, record:dict):
        column_values = dict()
        
        _report_to_db_subscription_event_column_names = {
            'Event Date' : 'event_date',
            'Event' : 'event',
            # 'App Name' : 'app_name',
            'App Apple ID' : 'app_apple_id',
            'Subscription Apple ID' : 'subscription_apple_id',
            'Subscription Group ID' : 'subscription_group_id',
            'Standard Subscription Duration' : 'standard_subscription_duration',
            # 'Subscription Offer Name' : 'subscription_offer_name',
            'Promotional Offer ID' : 'promotional_offer_id',
            'Subscription Offer Type' : 'subscription_offer_type',
            'Subscription Offer Duration' : 'subscription_offer_duration',
            'Original Start Date' : 'original_start_date',
            'Country' : 'country',
            'Days Before Canceling' : 'days_before_canceling',
            'Cancellation Reason' : 'cancellation_reason',
            'Days Canceled' : 'days_canceled',
            'Quantity' : 'quantity',
        }

        for key in record.keys():
            if key not in _report_to_db_subscription_event_column_names:
                continue
            
            column_values[_report_to_db_subscription_event_column_names[key]] = record[key]
            
       
        
       
        # 如果为' '，则转换为mysql 的空值
        for key, value in column_values.items():
            if value == ' ':
                column_values[key] = None
            elif type(value) == float and math.isnan(value):
                column_values[key] = None
                
        column_values['report_date'] = report_date.strftime('%Y-%m-%d')
                
        column_values = self._convert_subscription_event_value_types(column_values)
        
        columns = ', '.join(column_values.keys())
        placeholders = ', '.join(['%s'] * len(column_values))
         # Get the column values
        sql = f"INSERT INTO subscription_event ({columns}) VALUES ({placeholders})"
        

        result = cursor.execute(sql, list(column_values.values()))
        
        if result:
            print('Record inserted successfully. Date:', column_values['event_date'], '\tEvent:', column_values['event'], '\tSubscription Name:', Product.AppProduct.get_product_name_by_id(str(column_values['subscription_apple_id'])))
            
        self.db.commit()


    def _convert_subscription_event_value_types(self, column_values):

        if 'event' in column_values and column_values['event'] in subscription_events:
            column_values['event'] = subscription_events[column_values['event']]

        if 'standard_subscription_duration' in column_values and column_values['standard_subscription_duration'] in _standard_subscription_duration:
            column_values['standard_subscription_duration'] = _standard_subscription_duration[column_values['standard_subscription_duration']]

        if 'subscription_offer_type' in column_values and column_values['subscription_offer_type'] in _subscription_offer_type:
            column_values['subscription_offer_type'] = _subscription_offer_type[column_values['subscription_offer_type']]

        if 'subscription_offer_duration' in column_values and column_values['subscription_offer_duration'] in _subscription_offer_duration:
            column_values['subscription_offer_duration'] = _subscription_offer_duration[column_values['subscription_offer_duration']]
        
        if 'cancellation_reason' in column_values and column_values['cancellation_reason'] in _cancellation_reason:
            column_values['cancellation_reason'] = _cancellation_reason[column_values['cancellation_reason']]

        return column_values
    
    # 从数据库中获取订阅事件
    def get_subscription_events(self, start_date: str, end_date: str):
        cursor = self.db.cursor()
        sql = f"SELECT * FROM subscription_event WHERE event_date BETWEEN '{start_date}' AND '{end_date}'"
        cursor.execute(sql)
        records = cursor.fetchall()
        cursor.close()
        return records
    
    
    def add_exchange_rates(self, date:datetime.datetime, rates:list):
        print('Adding exchange rates to the database. Date:', date, 'Rates count:', len(rates))
        
        cursor = self.db.cursor()
        
        # 先删除表中report_date = report_date 的数据
        sql = f"DELETE FROM exchange_rates WHERE date = '{date}'"
        affect_count = cursor.execute(sql)
        self.db.commit()
        print('Deleted', affect_count, 'records from exchange_rates. Date:', date)
        
        for currency_code, rate in rates.items():
            
            self._add_exchange_rate(cursor=cursor, date=date, currenty_code=currency_code, rate=rate) 
            
        cursor.close()
    
    def _add_exchange_rate(self, cursor, date:datetime.datetime, currenty_code, rate):
        sql = "INSERT INTO exchange_rates (date, currency_code, rate) VALUES (%s, %s, %s)"
        cursor.execute(sql, (date.strftime("%Y-%m-%d"), currenty_code, rate))
        self.db.commit()
    
    def get_exhcange_rates(self, date: datetime.datetime):
        cursor = self.db.cursor()
        sql = f'SELECT date FROM exchange_rates order by abs(DATEDIFF(date, "{date.strftime("%Y-%m-%d")}")) asc limit 1'
        cursor.execute(sql)
        date_records = cursor.fetchall()
        nearest_date = date_records[0][0]


        sql = f'SELECT currency_code, rate FROM exchange_rates WHERE date="{nearest_date.strftime("%Y-%m-%d")}"'
        cursor.execute(sql)
        records = cursor.fetchall()
        cursor.close()
        
        # 将records转换为字典
        exchange_rates = dict()
        for record in records:
            exchange_rates[record[0]] = record[1]

        return exchange_rates